﻿CREATE PROCEDURE dbo.SP_News_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @CreationDate DateTime   =  NULL 
, @Description NVarChar (Max)  =  NULL 
, @Link NVarChar (256)  =  NULL 
, @NewsId Int   =  NULL 
, @Title NVarChar (50)  =  NULL 
	AS
			WITH CTE_News AS
			( 
			SELECT
 							CreationDate,
								Description,
								Link,
								NewsId,
								Title,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume='CreationDate' THEN CreationDate
						 
						WHEN @OrderColume='Description' THEN Description
						 
						WHEN @OrderColume='Link' THEN Link
												WHEN @OrderColume='NewsId' THEN NewsId
						 
						WHEN @OrderColume='Title' THEN Title
									ELSE 
							    NewsId
					        	END
			) AS "RowNumber"
  			FROM News 
			where 
								(CreationDate = @CreationDate OR @CreationDate Is Null)
										AND(Description LIKE @Description OR @Description Is Null)
										AND(Link LIKE @Link OR @Link Is Null)
										AND(NewsId = @NewsId OR @NewsId Is Null)
										AND(Title LIKE @Title OR @Title Is Null)
								)
			SELECT  *
			FROM CTE_News
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			